#!/usr/bin/python
# -*- coding: utf-8 -*-

import datetime
import glob
import os
import re
import shutil
import string
import sys
import time

import pepe.glib as g
import pepe.p_txt.bigtxt as bt
#import pepe.p_utils.oracle_dmp as odmp
import pepe.p_sql.oracle_dmp as odmp
import pepe.p_filesystem.basic as pfb
import pepe.p_filesystem.dfstats as dfs
from pepe.p_sql.oracle import oracle_log_export_done_in

dtnow = lambda : re.sub('[-. :]', '', str(datetime.datetime.now())[:])
d_tnow = lambda : time.strftime ("%Y%m%d_%H%M%S", time.localtime())

zipExtensions = [".zip", ".gz", ".rar", ".tar", ".z"]

n = '\x0D\x0A'


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # generic (not particular situation specific), reusable routines  # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


def delete_dir_contens(d):
    """Delete directory contens recursively
    
    We do not want to delete directory passed as parameter.
    Only its contens is to be deleted recursively.

    """
    for x in os.listdir(d):
        dx = d+'\x5C'+x
        if os.path.isdir(dx):
            # delete an entire directory tree
            # path must point to a directory
            shutil.rmtree(dx)
        if os.path.isfile(dx):
            os.remove(dx)

    if 0: # test procedure - ok
        delete_dir_contens(r'D:\viktor')


def files_list(dataDir):
    """Get list of files """
    return pfb.fslist(root=dataDir, patterns='*', recurse=True, return_folders=False)

    if 0: # procedure test - ok
        files = files_list(r'C:\AFT\pmfs\notepad2')
        for f in files:
            print f
        '''
        C:\AFT\pmfs\notepad2\License.txt
        C:\AFT\pmfs\notepad2\Notepad2.exe
        ...
        '''

def look_for_extension(filesDir, extensions):
    """Search recursivelly for file with given extension """
    r = False
    files = files_list(filesDir)
    for f in files:
        if os.path.splitext(f)[1] in extensions:
            return True
    return False


def is_compressed_file(f):
    """Decide whether it is a compressed file or not
    
    2011-02-08_1001 vp .... added .lower ... changed .Z to .z
    
    """
    extensions = [".zip", ".gz", ".tar", ".rar", ".tar", ".z"]
    if os.path.splitext(f)[1].lower in extensions:
        return True
    else:
        return False


def rows_in_dmp(f):
    """Returns number of rows from Oracle dmp files

    Works for Oracle dmp version 10+ 
    METRICET string not present in Oracle v9-

    """
    return odmp.srchMetricet(f)

    if 0: # procedure test - ok
        print rows_in_dmp('R01_BSIS_RU40_2010.dmp')
        print rows_in_dmp('R01_BSIS_RU40_2010.dmp')[9:]
        '''
        METRICET 28392
        28392
        '''


def file_info(f):
    """Get information about file """
    #full_path = os.path.join(dataDir, f)
    file_info = dfs.dfstats(f, options=[])
    file_info['full_path'] = f
    file_info['file_name'] = string.split(f, '\x5C')[-1]

    #We don't want seconds in modified date_time
    file_info['mtime_iso'] = file_info['mtime_iso'][:-3]
    
    return file_info
    
    if 0: # procedure test - ok
        print file_info('examine_files.py')
        #print file_info(r'C:\AFT\backup\Cyrill_Rawdata\R01_BSIS_RU40_2010.dmp')
        '''
        {'Archive_name': 'R01_EKPO_AT00_2007to2010.dmp', 'mtime_iso': ' ... 
         'mtime_plain': '20101006120611', 'file_name': 'R01_EKPO_AT00_2 ... 
        h': 'R01_EKPO_AT00_2007to2010.dmp', 'size': 637952L}
        {'Archive_name': 'R01_BSIS_RU40_2010.dmp', 'mtime_iso': '2010-1 ... 
        e_plain': '20101005082624', 'file_name': 'R01_BSIS_RU40_2010.dm ...
        R01_BSIS_RU40_2010.dmp', 'size': 8716288L}
        '''


def check_7z_log(f):
    """Check 7zip log file and report successful extraction """
    if os.path.exists(f):
        data = open(f, 'r').read()
        if re.search('Everything is Ok', data):
            return True
    return False
        
    if 0: # testing procedure - ok
        # checking nonexistent file
        print check_7z_log(r'.\logs\20110201192114184000_LHGL Data.zip.extract')
        # checking existent and OK log file
        print check_7z_log(r'.\logs\20110201192114184000_LHGL Data.zip.extract.log')


def examine_oracle_log(f):
    """Check files with .log extension (Oracle log file) 
    and try to extract information how many rows were exported.

    Example of log file:

        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options
        Export done in UTF8 character set and UTF8 NCHAR character set
        
        About to export specified tables via Conventional Path ...
        Current user changed to SAPSR3
        . . exporting table                           BSAD          0 rows exported
        Export terminated successfully without warnings.

    """
    r = {'ora_log_success': '', 'rows_exported': '', 'dmp_export_done_in': ''}
    if os.path.exists(f) and os.path.splitext(f)[1] in ('.log', '.LOG'):
    
        r['dmp_export_done_in'] = oracle_log_export_done_in(f)
    
        data = open(f, 'r').read()

        if re.search('Export terminated successfully without warnings', data):
            r['ora_log_success'] = 's'
        elif re.search('successfully completed at \d{2}\:\d{2}\:\d{2}\x0A', data):
            r['ora_log_success'] = 's'
        elif re.search('Export terminated successfully with warnings', data):
            r['ora_log_success'] = 'sw'
        elif re.search('Export terminated unsuccessfully', data):
            r['ora_log_success'] = 'u'
            
        s3 = re.search("(EXP.\d{5}.{1,})\x0A", data)
        if s3: 
            r['rows_exported'] = str(s3.group(1))

        s2 = re.search("(.{11}ORACLE error.{1,})\x0A", data)
        if s2: 
            r['rows_exported'] = str(s2.group(1))

        s1 = re.search("(\d{1,}) rows exported", data)
        if s1: 
            r['rows_exported'] = str(s1.group(1))

    return r
        
    if 0: # testing procedure - ok
        # checking nonexistent file
        print examine_oracle_log(r'A:\SAP LH1\2010-10-11\original\Cherly_RU40_LH1\LH1_BSIS_RU40_2010.log_')
        # checking existent and OK log file
        print examine_oracle_log(r'A:\SAP LH1\2010-10-11\original\Cherly_RU40_LH1\LH1_BSIS_RU40_2010.log')


def file_extract(f, outDir, logDir, rmZip=False):
    """Extraction of compressed file
    
    f       - file with full path to be extracted
    tempDir - directory where to extract given file
    logDir  - log directory where to store log files
    rmZip   - remove compressed source file after sucessfull extraction

    """
    #Use this if there are any .rar files
    #commandString = "7z x -r -y -pHP2DT -o\"" + dataDir + "\\dce\\\"* \"" + os.path.join(dataD
    #ir, f) + "\" -- > \""+ logDir + str(counter) + "_" + f + ".extract.log\""
    
    # create log directory if des not exists
    if not os.path.exists(logDir):
        os.mkdir(logDir)
    
    fname = string.split(f, '\x5C')[-1]
    
    dt = dtnow()
    
    # use this if there are any .tar files
    ca = r"""C:\aft\pmfs\7za920\7za x -r -y -pHP2DT -o""" 

    cb = "\"%s*_xXx%s\" \"%s\" -- > \"%s%s_%s.extract.log\"" % (outDir, dtnow(), f, logDir, dt, fname)
    # do not use 'folders timestamping for tar files
    #    because it creates the same exrtacted directory 
    #    so many times as the number of files in extracted directory
    if os.path.splitext(f)[1].lower() == '.tar':
        cb = "\"%s*\" \"%s\" -- > \"%s%s_%s.extract.log\"" % (outDir, f, logDir, dt, fname)

    #print ca + cb
    os.system(ca + cb)

    logfile = "%s%s_%s.extract.log" % (logDir, dt, fname)
    
    # remove zip file if extraction was sucessfull
    #if rmZip == True and check_7z_log(logfile) == True: 
    # remove even if unsuccessfuls
    if rmZip == True: os.remove(f)
    
    #delete dce
    #if os.path.exists(os.path.join(tempDir, "ext")):
    #	shutil.rmtree(os.path.join(tempDir, "dce"))
    return logfile

    if 0: # test procedure - ok
        # A:\SAP FI1\2010-11-11
        simpleExtract(f=r'A:\SAP FUSION\2010-10-19\original\R01_VBRK_AT00_2007to2010.dmp.gz', 
                      outDir=r'D:\viktor' + '\x5C', 
                      logDir=r'D:\viktor\logs' + '\x5C')


def dir_recursive_extract(filesDir, logDir, zipExt, removeSource=False):
    """Recursive extract of compressed files in directory

    f       - file with full path to be extracted
    outDir  - directory where to extract given file
    logDir  - log directory where to store log files
    zipExt  - extensions that indicate compressed files

    """
    while look_for_extension(filesDir, extensions=zipExt):

        # get list of files to be processed
        files = files_list(filesDir)
        
        # process files
        for f in files:
            if os.path.splitext(f)[1] in zipExt:
                #print f
                outDir = string.join(string.split(f, '\x5C')[:-1], '\x5C') + '\x5C'
                file_extract(f, outDir, logDir, rmZip=True)

    if 0: # test procedure - ok

        fd = r'D:\viktor' + '\x5C'
        ld = os.getcwd() + "\\logs" + '\x5C'
        ze = [".zip", ".gz", ".tar", ".rar", ".tar", ".Z"]
    
        file_extract(  f=r'A:\SAP LH1\2010-07-28\original\LHGL Data.zip',
                      outDir=fd,
                      logDir=ld)
        dir_recursive_extract(filesDir=fd, logDir=ld, zipExt=ze)


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # particular situation specific (not directly reusable) routines  # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


def files_list_filtered(dataDir):
    fs = files_list(dataDir)
    fs2 = []
    for f in fs:
        # exclude directories and 'extracted', 'pwc' ...
        if (f.find("\\extracted") < 1) and f.find("(PwC)") < 1:
            fs2.append(f)
    return fs2


def find_in_filename(f):
    """Find year, table and company code in file name (only; not whole path) """
    r = {'fnYear': '', 'fnTable': '', 'fnCompanyCode': ''}

    # we wanna search in filename only, not whole path
    #   example .... A:\SAP LH1\2010-09-20\zip_files\BKPF_AT00_2007_2008_METADATA.txt.Z
    #   should return '2007 2008' ... not '2007 2008 2010'
    fname = string.split(f, '\x5C')[-1]

    years = [       '1999', '2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010']
    tables = [      'TSTC', 'VBRP','ZARIXFI1','BKPF','BSAD','BSAK','BSAS','BSEC','BSEG',
                    'BSID','BSIK','BSIS','CSKS','CSKT','d03L','EKBE','EKKN','EKKO','EKPO',
                    'KNA1','KNB1','KNBK','LFA1','LFB1','LFBK','MARA','MKPF','MSEG','OBJK',
                    'OBKT','PAYR','REGUC','REGUH','RFBLG','SKA1','SKAT','SKB1','T001',
                    'T003','T003T','T005','T005T','TBSL','TBSLT','TGSB','TVFK','USR03',
                    'VBAK','VBAP','VBRK','ZARIXMM1','ZARIXMM2','ZARIXMM4','ZARIXMM6',
                    'ZARIXSD6','ZARIXXFI1']
    # company codes
    ccodes = [      'AT00','CH60','CH70','DE00','DE50','DE94','NL60','RU00','S500','US95',
                    'CC','ML60','CH00','RU40','RU401', 'US00', 'US1']
    
    # find years in filename
    for year in years:
        if fname.find(year) > -1:
            r['fnYear'] += year + ' '
    r['fnYear'] = r['fnYear'].strip()
    if len(r['fnYear']) < 1:
        r['fnYear'] = '*'

    # find tables in filename
    for table in tables:
        if fname.find(table) > -1:
            r['fnTable'] += table + ' '
    r['fnTable'] = r['fnTable'].strip()
    if len(r['fnTable']) < 1:
        r['fnTable'] = '*'
        
    # find company code's in filename
    for cc in ccodes:
        if fname.find(cc) > -1:
            r['fnCompanyCode'] += cc + ' '
    r['fnCompanyCode'] = r['fnCompanyCode'].strip()
    if len(r['fnCompanyCode']) < 1:
        r['fnCompanyCode'] = '*'
    
    return r

    if 0: # procedure test - ok
        r = find_in_filename(r'C:\AFT\pmfs\notepad2\R01_EKPO_AT00_2007to2010.dmp')
        print r
        '''
        {'fnYear': '2007 2010', 'fnCompanyCode': 'AT00', 'fnTable': 'EKPO'}
        '''


def system_name(f):
    """Extract system name from f (path/file) """
    # input  : Cyrill_Rawdata\SAP LH1\2010-09-20\zip_files\BKPF_AT00_2008.txt.Z
    # output : LH1
    system_name = ''
    if f.find("\\") <> -1:
        system_name = str(f[str(f).find('\\SAP ')+5:])
        system_name = system_name[:system_name.find("\\")]
    return system_name


def print_file_info(finfo, ohandle, override={}):
    """Pretty print results """
    
    if finfo['raw_or_ext'] == 'raw data':
        fp = finfo['full_path_raw']
    else:
        fp = finfo['path_extracted']
    
    # find year, table and company code in file path/name
    fif = find_in_filename(fp)

    # file extension
    finfo['file_ext'] = os.path.splitext(fp)[1].lower()

    # examine if oracle log files
    finfo.update(examine_oracle_log(fp))
    
    # check metricet
    if os.path.splitext(fp)[1].lower() == '.dmp': 
        finfo['metricet_rows_count'] = rows_in_dmp(fp)
    else:
        finfo['metricet_rows_count'] = ''

    finfo['extract_temp_dir'] = ''

    # apply override parameters if any
    finfo.update(override)

    # paths pretty print; keep it last to keep path functional if used
    finfo['full_path_raw'] = re.sub('A:', 'Cyrill_Rawdata', finfo['full_path_raw'])
    finfo['path_extracted'] = finfo['path_extracted'][len(finfo['extract_temp_dir']):]

    ohandle.write(string.join( [ finfo['system_name']
                                , fif['fnTable']
                                , fif['fnCompanyCode']
                                , fif['fnYear']
                                , finfo['full_path_raw']
                                , finfo['path_extracted']
                                , finfo['file_name']
                                , str(finfo['size'])
                                , finfo['mtime_iso'] 
                                , str(finfo['size']) + finfo['mtime_iso'] 
                                , ''
                                , ''
                                , finfo['raw_or_ext']
                                , finfo['file_ext']
                                , finfo['ora_log_success']
                                , finfo['rows_exported']
                                , finfo['metricet_rows_count']
                                , finfo['dmp_export_done_in']
                                , n], '\t'))


def process_raw_data_file(fr, ohandle=False, ehandle=False):
    """Returns size, path, filename ... information about file """

    fname = string.split(fr, '\x5C')[-1]
    #try:
    finfo = file_info(fr)
    finfo.update({'raw_or_ext': 'raw data'})

    # get system_name variable
    finfo['system_name'] = system_name(fr)

    finfo['full_path_raw'] = finfo['full_path'][:]
    finfo['path_extracted'] = ''
    print_file_info(finfo, ohandle)

    #except Exception, ex:
    #    if ehandle <> False: ehandle.write('1:\t' + fr + "\t" + str(type(ex)) + "\t" + str(ex) + n)


def process_extracted_file(fe, override={}, ohandle=False, ehandle=False):
    """Returns size, path, filename ... information about file """

    fname = string.split(fe, '\x5C')[-1]
    #try:
    finfo = file_info(fe)
    finfo['path_extracted'] = fe
    finfo.update({'raw_or_ext': 'extracted'})

    # override file extension
    override['file_ext'] = os.path.splitext(fe)[1].lower()

    print_file_info(finfo, ohandle, override)

    #except Exception, ex:
    #    if ehandle <> '': ehandle.write('2:\t' + fe + "\t" + str(type(ex)) + "\t" + str(ex) + n)


def process_zip_file(fz, outDir, logDir, ohandle=False, ehandle=False):
    """Extract recursivelly and examine contens of compressed file
    
    Extract source compressed file (recursively)
    Get file info metadata for all non-zip files
    Delete extracted files/folders

    Interesting examples:  

        zip in zip: SAP LH1\2010-07-28\original

        From: Tschammer, Martin (DK - Copenhagen) 
        Sent: Monday, January 31, 2011 9:45 AM
        Cyrill_Rawdata\SAP SAIL\2010-09-01\
        For some reason, the compressed files in this directory caused some issues 
        with earlier versions of the 7z program. Other than everything was problem-free.

    """
    #try:

    # do not waste time with copying the file, extract directly to destination
    #shutil.copy(f, tempDir + fname)

    file_extract(fz, outDir, logDir)
    dir_recursive_extract(outDir, logDir, zipExt=zipExtensions)

    # override system_name variable
    override = {}
    override['system_name'] = system_name(fz)
    override['full_path_raw'] = fz
    override['extract_temp_dir'] = outDir

    # get list of files to be processed
    files = files_list(outDir)

    # process extracted files
    for fe in files:
        process_extracted_file(fe, override, ohandle, ehandle)

    #except Exception, ex:
    #    if ehandle <> False: ehandle.write('3:\t' + fz + "\t" + str(type(ex)) + "\t" + str(ex) + n)


##
## CLIENT SIDE
##

"""
#!/usr/bin/python
# -*- coding: utf-8 -*-

import datetime
import os
import time
import pepe.p_filesystem.cyrill_examine_files as cef

dtnow = lambda : re.sub('[-. :]', '', str(datetime.datetime.now())[:])
d_tnow = lambda : time.strftime ("%Y%m%d_%H%M%S", time.localtime())
n = '\x0D\x0A'

if 1: ## setup

    # options
    extractZip = True
    unzipTempDir = r'D:\viktor\_FI1' + '\x5C'
    unzipLogDir = os.getcwd() + "\\logs" + '\x5C'
    
    # output files
    o = open('files.csv', 'wb')
    e = open('errors.csv', 'wb')
    p = open('processed_files.csv', 'wb')

    o.write('system\ttable\tcompany_code\tyear' + \
            '\tfull_path_raw\tpath_extracted\tfile_name' + \
            '\tsize\tmodified_date_time\tkey' + \
            '\tstatus\tteam_member\traw_or_extracted\tfile_ext' + \
            '\tora_log_success\trows_exported\tmetricet_rows_count' + n)
    e.write('e_no\tfile_name\texception_type\texception' + n)

    # paths
    #rawDataDirs = [r'A:\SAP LH1\2010-07-28']
    #rawDataDirs = [r'A:\SAP LH1\2010-09-20']
    #rawDataDirs = [r'A:\SAP FI1', r'A:\SAP FUSION', r'A:\SAP SAIL', r'A:\SAP LH1']
    rawDataDirs = [r'A:\SAP FI1']
    #rawDataDirs = [r'A:\SAP LH1\2010-10-11', r'A:\SAP LH1\2010-07-28']

    for rawDataDir in rawDataDirs:

        rawDataDir = rawDataDir + '\x5C'
        # get list of files to be processed
        files = cef.files_list_filtered(rawDataDir)
        # process files
        for rdf in files:
            print rdf
            p.write('start\t' + rdf + '\t' + d_tnow() + n)
            cef.process_raw_data_file(      rdf, 
                                            ohandle=o,
                                            ehandle=e)
            # if zip extract and examine its contens
            if extractZip == True and 1==1:
                if os.path.splitext(rdf)[1] in cef.zipExtensions:
                    cef.process_zip_file(   rdf, 
                                            outDir=unzipTempDir, 
                                            logDir=unzipLogDir, 
                                            ohandle=o, 
                                            ehandle=e)
            # cleanup decompress directory
            cef.delete_dir_contens(unzipTempDir)
            p.write('finish\t' + rdf + '\t' + d_tnow() + n)

    o.close(); e.close(); p.close()
"""